import sqlite3
from typing import List, Tuple, Optional

class DatabaseManager:
    def __init__(self, db_path: str = "opcua_points.db"):
        self.db_path = db_path
        self.init_database()

    def init_database(self):
        """初始化数据库和表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建OPC UA点位表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS opcua_points (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                url TEXT NOT NULL,
                node_id TEXT NOT NULL,
                enabled INTEGER DEFAULT 1
            )
        ''')
        
        conn.commit()
        conn.close()

    def add_point(self, name: str, url: str, node_id: str) -> int:
        """添加新的OPC UA 点位"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('''
            INSERT INTO opcua_points (name, url, node_id)
            VALUES (?, ?, ?)
        ''', (name, url, node_id))
        
        point_id = cursor.lastrowid
        conn.commit()
        conn.close()
        return point_id
    
    def get_all_points(self) -> List[Tuple]:
        """获取所有OPC UA点位"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM opcua_points ORDER BY id')
        points = cursor.fetchall()
        
        conn.close()
        return points

    def get_point_by_id(self, point_id: int) -> Optional[Tuple]:
        """根据ID获取特定点位"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('SELECT * FROM opcua_points WHERE id = ?', (point_id,))
        point = cursor.fetchone()
        
        conn.close()
        return point

    def update_point(self, point_id: int, name: str, url: str, node_id: str) -> bool:
        """更新OPC UA点位信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('''
            UPDATE opcua_points
            SET name = ?, url = ?, node_id = ?
            WHERE id = ?
        ''', (name, url, node_id, point_id))
        
        rows_affected = cursor.rowcount
        conn.commit()
        conn.close()
        return rows_affected > 0

    def delete_point(self, point_id: int) -> bool:
        """删除OPC UA点位"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('DELETE FROM opcua_points WHERE id = ?', (point_id,))
        
        rows_affected = cursor.rowcount
        conn.commit()
        conn.close()
        return rows_affected > 0

    def toggle_point_status(self, point_id: int) -> bool:
        """切换点位启用/禁用状态"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 获取当前状态
        cursor.execute('SELECT enabled FROM opcua_points WHERE id = ?', (point_id,))
        result = cursor.fetchone()
        if not result:
            conn.close()
            return False
            
        current_status = result[0]
        new_status = 0 if current_status == 1 else 1
        
        # 更新状态
        cursor.execute('UPDATE opcua_points SET enabled = ? WHERE id = ?', (new_status, point_id))
        
        conn.commit()
        conn.close()
        return True